package dao;

import model.Conn;
import model.Goods;
import model.Recommend;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * goods表操作
 */
public class GoodsDao {
    Connection connection = null;
    PreparedStatement pStatement = null;
    Conn conn = new Conn();
    ResultSet rs = null;

    /**
     * 获取轮播的商品信息
     *
     * @return List
     * @throws SQLException
     */
    public List<Goods> getScroll() throws SQLException {
        String sql = "select * from goods,recommend where goods.id = recommend.goods_id";
        connection = conn.getConn();
        pStatement = connection.prepareStatement(sql);
        rs = pStatement.executeQuery();
        List<Goods> scroll = new ArrayList<Goods>();
        while (rs.next()) {
            Goods goods = new Goods();
            goods.setCover(rs.getString(3));
            goods.setId(rs.getInt(1));
            goods.setName(rs.getString("name"));
            scroll.add(goods);
        }
        Conn.ClossAll(connection, pStatement, rs);
        return scroll;
    }

    /**
     * 获取推荐的商品信息
     *
     * @param recome 推荐的type 2为热销 3为新品
     * @return List
     * @throws SQLException
     */
    public List<Goods> getHost(int recome) throws SQLException {
        String sql = "select g.id,g.name,g.cover,g.price,t.name typename from recommend r,goods g,type t where type=? and r.goods_id=g.id and g.type_id=t.id";
        connection = conn.getConn();
        pStatement = connection.prepareStatement(sql);
        pStatement.setInt(1, recome);
        rs = pStatement.executeQuery();
        List<Goods> scroll2 = new ArrayList<Goods>();
        while (rs.next()) {
            Goods goods = new Goods();
            goods.setId(rs.getInt("id"));
            goods.setName(rs.getString("name"));
            goods.setCover(rs.getString(3));
            goods.setPrice(rs.getInt("price"));
            goods.setTypeName(rs.getString("typename"));
            scroll2.add(goods);
        }
        Conn.ClossAll(connection, pStatement, rs);
        return scroll2;
    }


    /**
     * 获取商品的总数通过typeid
     *
     * @param typeID 商品类型 0为全部商品
     * @return int 总量
     * @throws SQLException
     */
    public int getGoodsCount(int typeID) throws SQLException {
        String sql = null;
        int goodsCount = 0;
        if (typeID == 0) {
            sql = "select count(*) acc from goods";
            pStatement = conn.getConn().prepareStatement(sql);
            rs = pStatement.executeQuery();
        } else {
            sql = "select count(*) acc from goods where type_id=?";
            pStatement = conn.getConn().prepareStatement(sql);
            pStatement.setInt(1, typeID);
            rs = pStatement.executeQuery();
        }
        rs.next();
        goodsCount = rs.getInt("acc");
        Conn.ClossAll(connection, pStatement, rs);
        return goodsCount;
    }

    /**
     * 获取某个系列的商品通过type_id
     *
     * @param typeID     类别id 0为全部的商品
     * @param pagenumber 第几页
     * @param pageSize   一页的数量
     * @return List 商品集合
     * @throws SQLException
     */
    public List<Goods> selectGoodsByTypeID(int typeID, int pagenumber, int pageSize) throws SQLException {
        List<Goods> goodsList = new ArrayList<Goods>();
        String sql;
        if (typeID == 0) {
            sql = "select * from goods limit ?,?";
            pStatement = conn.getConn().prepareStatement(sql);
            pStatement.setInt(1, (pagenumber - 1) * pageSize);
            pStatement.setInt(2, pageSize);
        } else {
            sql = "select * from goods where type_id=? limit ?,?";
            pStatement = conn.getConn().prepareStatement(sql);
            pStatement.setInt(1, typeID);
            pStatement.setInt(2, (pagenumber - 1) * pageSize);
            pStatement.setInt(3, pageSize);
        }
        rs = pStatement.executeQuery();
        while (rs.next()) {
            Goods goods = new Goods();
            goods.setId(rs.getInt("id"));
            goods.setName(rs.getString("name"));
            goods.setCover(rs.getString(3));
            goods.setPrice(rs.getInt("price"));
            goodsList.add(goods);
        }
        Conn.ClossAll(connection, pStatement, rs);
        return goodsList;
    }

    /**
     * 获取商品详情
     *
     * @param id 商品id
     * @return Goods || null
     * @throws SQLException
     */
    public Goods getGoodsDetail(int id) throws SQLException {
        String sql = "select g.id,g.name,g.cover,g.image1,g.image2,g.price,g.intro,g.stock,t.id typeid,t.name typename from goods g,type t where g.id=? and g.type_id=t.id";
        connection = conn.getConn();
        pStatement = connection.prepareStatement(sql);
        pStatement.setInt(1, id);
        rs = pStatement.executeQuery();
        Goods goods = null;
        try {
            if (rs.next()) {
                goods = new Goods();
                goods.setId(rs.getInt("id"));
                goods.setName(rs.getString("name"));
                goods.setCover(rs.getString("cover"));
                goods.setImage1(rs.getString("image1"));
                goods.setImage2(rs.getString("image2"));
                goods.setPrice(rs.getFloat("price"));
                goods.setIntro(rs.getString("intro"));
                goods.setStock(rs.getInt("stock"));
                goods.setTypeid(rs.getInt("typeid"));
                goods.setTypeName(rs.getString("typename"));

                return goods;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        Conn.ClossAll(connection, pStatement, rs);
        return goods;
    }

    /**
     * 获取商品详情通过类型
     *
     * @param type       类型 0表示查全部商品 1 条幅 2 热销 3 新品
     * @param pageNumber 页码
     * @param pageSize   一页的数量
     * @return List<Goods>
     * @throws SQLException
     */
    public List<Goods> selectGoodsByType(int type, int pageNumber, int pageSize) throws SQLException {
        String sql = "";
        connection = this.conn.getConn();
        //等于0的话查全部商品
        if (type == 0) {
            sql = "SELECT goods.*, type.`name` AS typename  FROM goods INNER JOIN type ON goods.type_id = type.id ORDER BY goods.id ASC LIMIT ?,?";
            pStatement = connection.prepareStatement(sql);
            pStatement.setInt(1, pageNumber);
            pStatement.setInt(2, pageSize);
        } else {
            sql = "SELECT goods.*, type.`name` AS typename,recommend.type AS type FROM goods INNER JOIN type ON goods.type_id = type.id INNER JOIN recommend ON goods.id = recommend.goods_id WHERE type = ? ORDER BY goods.id ASC LIMIT ?, ?";
            pStatement = connection.prepareStatement(sql);
            pStatement.setInt(1, type);
            pStatement.setInt(2, pageNumber);
            pStatement.setInt(3, pageSize);
        }

        rs = pStatement.executeQuery();
        List<Goods> goodsList = new ArrayList<>();
        while (rs.next()) {
            Goods goods = new Goods();
            Recommend recommend = new Recommend();
            List<Recommend> recmmendList = new ArrayList<>();
            goods.setId(rs.getInt("id"));
            goods.setName(rs.getString("name"));
            goods.setCover(rs.getString("cover"));
            goods.setImage1(rs.getString("image1"));
            goods.setPrice(rs.getFloat("price"));
            goods.setIntro(rs.getString("intro"));
            goods.setTypeName(rs.getString("typename"));
            recommend.setType(type);
            recmmendList.add(recommend);
            goods.setList(recmmendList);
            goodsList.add(goods);
        }
        Conn.ClossAll(connection, pStatement, rs);
        return goodsList;
    }

    /**
     * 查询是否有指定的商品
     *
     * @param goodsId 商品id
     * @return boolean
     * @throws SQLException
     */
    public boolean hasGoods(int goodsId) throws SQLException {
        String sql = "SELECT * FROM `goods` WHERE id = ?";
        connection = conn.getConn();
        pStatement = connection.prepareStatement(sql);
        pStatement.setInt(1, goodsId);
        rs = pStatement.executeQuery();
        boolean flag = rs.next();
        Conn.ClossAll(connection, pStatement, rs);
        return flag;
    }

    /**
     * 添加商品
     *
     * @param goods 商品对象
     * @return boolean
     * @throws SQLException
     */
    public boolean addGoods(Goods goods) throws SQLException {
        String sql = "INSERT INTO `cakeshop`.`goods` (`name`, `cover`, `image1`, `image2`, `price`, `intro`, `stock`, `type_id`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        connection = conn.getConn();
        pStatement = connection.prepareStatement(sql);
        pStatement.setString(1, goods.getName());
        pStatement.setString(2, goods.getCover());
        pStatement.setString(3, goods.getImage1());
        pStatement.setString(4, goods.getImage2());
        pStatement.setFloat(5, goods.getPrice());
        pStatement.setString(6, goods.getIntro());
        pStatement.setInt(7, goods.getStock());
        pStatement.setInt(8, goods.getType().getId());
        int i = pStatement.executeUpdate();
        Conn.ClossAll(connection, pStatement, rs);
        return i > 0;
    }

    public boolean updateGoods(Goods goods) throws SQLException {
        String sql = "UPDATE `cakeshop`.`goods` SET `name` = ?, `cover` = ?, `image1` = ?, `image2` = ?, `price` = ?, `intro` = ?, `stock` = ?, `type_id` = ? WHERE `id` = ?";
        connection = conn.getConn();
        pStatement = connection.prepareStatement(sql);
        pStatement.setString(1, goods.getName());
        pStatement.setString(2, goods.getCover());
        pStatement.setString(3, goods.getImage1());
        pStatement.setString(4, goods.getImage2());
        pStatement.setFloat(5, goods.getPrice());
        pStatement.setString(6, goods.getIntro());
        pStatement.setInt(7, goods.getStock());
        pStatement.setInt(8, goods.getType().getId());
        pStatement.setInt(9, goods.getId());
        int i = pStatement.executeUpdate();
        Conn.ClossAll(connection, pStatement, rs);
        return i > 0;
    }

    /**
     * 删除商品通过id
     *
     * @param id
     * @return
     * @throws SQLException
     */
    public boolean deleteGoodsById(int id) throws SQLException {
        String sql = "DELETE FROM `cakeshop`.`goods` WHERE `id` = ?";
        connection = conn.getConn();
        pStatement = connection.prepareStatement(sql);
        pStatement.setInt(1, id);
        int i = pStatement.executeUpdate();
        Conn.ClossAll(connection, pStatement, rs);
        return i > 0;
    }
}
